package org.kl.auditsupervision.repository.fnd;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.kl.auditsupervision.dto.audit.RiskDimensionStatistic;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Component;

@Component
public class RiskDimensionStatisticDao {
	@Autowired
	JdbcTemplate jdbcTemplate;

	public List<RiskDimensionStatistic> getRiskDimensionStatistic(String startYear,String endYear,String type,
			int pageNumber, int pageSize, String sortType, String orderType) {
		final List<RiskDimensionStatistic> result = new ArrayList<>();
		// oracle的SQL语句
		StringBuilder sql = new StringBuilder();
		
		String sqlString=getSql(startYear,endYear,type, sortType, orderType);
		if(pageNumber==0&&pageSize==0){
			//excel不分页
		}else{
			sqlString=getPaginationSql(sqlString,pageNumber,pageSize);
		}
		sql.append(sqlString);

		this.jdbcTemplate.query(sql.toString(), new RowCallbackHandler() {
			int i = 0;

			@Override
			public void processRow(ResultSet rs) throws SQLException {
				RiskDimensionStatistic riskDimensionStatistic = new RiskDimensionStatistic();
				riskDimensionStatistic.setChildDept(rs.getString("org_name"));
				riskDimensionStatistic.setHfhg(rs.getString("hfhg"));
				riskDimensionStatistic.setFzzl(rs.getInt("fzzl"));
				riskDimensionStatistic.setXlhxg(rs.getString("xlhxg"));
				riskDimensionStatistic.setXxzswz(rs.getString("xxzswz"));
				riskDimensionStatistic.setZcaq(rs.getString("zcaq"));
				riskDimensionStatistic.setCount(rs.getString("count"));
				i++;
				riskDimensionStatistic.setDisplaySeq(Integer.parseInt(i + "0"));
				result.add(riskDimensionStatistic);
			}
		});
		return result;
	}

	private String getPaginationSql(String sql, int pageNumber, int pageSize) {
		String startRow = String.valueOf(pageSize * (pageNumber-1));
		String endRow = String.valueOf(pageSize * pageNumber);
		String paginationSql = "";
		paginationSql = "select * from (select row_.*, rownum rownum_ from ("
				+ sql + ")row_ where rownum <= " + endRow
				+ ")where rownum_ > " + startRow;
		return paginationSql;
	}
	
	private String getSql(String startYear,String endYear,String type, String sortType, String orderType){
		String sqlString = "";
	
		sqlString += "select '项数' as org_name, "+
       "count(decode(de.qxyxfl, '合法合规', 1)) as hfhg, "+
       "count(decode(de.qxyxfl, '资产安全', 1)) as zcaq, "+
       "count(decode(de.qxyxfl, '信息真实完整', 1)) as xxzswz, "+
       "count(decode(de.qxyxfl, '效率和效果', 1)) as xlhxg, "+
       "count(decode(de.qxyxfl, '发展战略', 1)) as fzzl, "+
       "count(de.qxyxfl) as count "+
  "from sjjc_audit_defect de "+
  "left join sjjc_audit_details dtl on de.audit_pro_dtl_id = dtl.id "+
  "left join sjjc_audit a on a.id = dtl.audit_pro_id "+
  "left join fnd_org o on o.id = dtl.org_id where 1=1";
		  if(StringUtils.isNotBlank(startYear)){
			  sqlString+=" and to_char(dtl.create_time, 'YYYY') >= '"+startYear+"' ";
		  }
		  if(StringUtils.isNotBlank(endYear)){
			  sqlString+=" and to_char(dtl.create_time, 'YYYY') <= '"+endYear+"' ";
		  }
		  if(StringUtils.isNotBlank(type)){
			  sqlString+=" and a.type = '"+type+"' ";
		  }
		  sqlString+="union all "+
"select '比例(%)', "+
       "round(count(decode(de.qxyxfl, '合法合规', 1)) / count(de.qxyxfl) * 100, 0) as hfhg, "+
       "round(count(decode(de.qxyxfl, '资产安全', 1)) / count(de.qxyxfl) * 100, 0) as zcaq, "+
       "round(count(decode(de.qxyxfl, '信息真实完整', 1)) / count(de.qxyxfl) * 100,0) as xxzswz, "+
       "round(count(decode(de.qxyxfl, '效率和效果', 1)) / count(de.qxyxfl) * 100, 0) as xlhxg, "+
       "round(count(decode(de.qxyxfl, '发展战略', 1)) / count(de.qxyxfl) * 100, 0) as fzzl, "+
       "1 "+
  "from sjjc_audit_defect de "+
  "left join sjjc_audit_details dtl on de.audit_pro_dtl_id = dtl.id "+
  "left join sjjc_audit a on a.id = dtl.audit_pro_id "+
  "left join fnd_org o on o.id = dtl.org_id where 1=1";
		  if(StringUtils.isNotBlank(startYear)){
			  sqlString+=" and to_char(dtl.create_time, 'YYYY') >= '"+startYear+"' ";
		  }
		  if(StringUtils.isNotBlank(endYear)){
			  sqlString+=" and to_char(dtl.create_time, 'YYYY') <= '"+endYear+"' ";
		  }
		  if(StringUtils.isNotBlank(type)){
			  sqlString+=" and a.type = '"+type+"' ";
		  }
  sqlString+="union all "+
"select t.org_name, "+
       "count(decode(t.qxyxfl, '合法合规', 1)) as hfhg, "+
       "count(decode(t.qxyxfl, '资产安全', 1)) as zcaq, "+
      " count(decode(t.qxyxfl, '信息真实完整', 1)) as xxzswz, "+
       "count(decode(t.qxyxfl, '效率和效果', 1)) as xlhxg, "+
      "count(decode(t.qxyxfl, '发展战略', 1)) as fzzl, "+
       "count(t.qxyxfl) as count "+
  "from (select o.org_name, de.* "+
          "from sjjc_audit_defect de "+
          "left join sjjc_audit_details dtl on de.audit_pro_dtl_id = dtl.id "+
          "left join sjjc_audit a on a.id = dtl.audit_pro_id "+
          "left join fnd_org o on o.id = dtl.org_id where 1=1";
        		  if(StringUtils.isNotBlank(startYear)){
        			  sqlString+=" and to_char(dtl.create_time, 'YYYY') >= '"+startYear+"' ";
        		  }
        		  if(StringUtils.isNotBlank(endYear)){
        			  sqlString+=" and to_char(dtl.create_time, 'YYYY') <= '"+endYear+"' ";
        		  }
        		  if(StringUtils.isNotBlank(type)){
        			  sqlString+=" and a.type = '"+type+"' ";
        		  }
          sqlString+=") t group by t.org_name ";

		return sqlString;
	}
	
	public int getRiskDimensionStatisticCount(String startYear,String endYear,String type) {

		// oracle的SQL语句
		StringBuilder sql = new StringBuilder();
		
		String sqlString=getSql(startYear,endYear,type,"","");

		sql.append("select count(1) as count from ("+sqlString+")");
		
		Integer count=(Integer)this.jdbcTemplate.queryForObject(sql.toString(),Integer.class);
		
		int coungInt=count.intValue();

		return coungInt;
	}

}
